﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using OBUCare.Core.Models;
using OBUCare.Core.Common;
using System.Data.SqlClient;
using System.Data;

namespace OBUCare.Core.DataAccess
{
    public class ISSUE_RPTContext : DbContext
    {


        static ISSUE_RPTContext()
        {
            Database.SetInitializer<OBU_CAREContext>(null);
        }

        public ISSUE_RPTContext()
            : base("Name=ISSUE_RPTContext")
        {
        }

        /// <summary>
        /// 登录，并返回用户信息
        /// </summary>
        /// <param name="operNo"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public OPERATOR GetOPERATOR(string operNo, string pwd)
        {
            string sql = string.Format("select * from OPERATORS where OPERATOR_NO='{0}' and OPERATOR_PASSWORD='{1}'", operNo, pwd);
            return base.Database.SqlQuery<OPERATOR>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 返回用户信息
        /// </summary>
        /// <param name="operNo"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public OPERATOR GetOPERATOR(string operNo)
        {
            string sql = string.Format("select * from OPERATORS where OPERATOR_NO='{0}'", operNo);
            return base.Database.SqlQuery<OPERATOR>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 登录，并返回用户信息
        /// </summary>
        /// <param name="operNo"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public IEnumerable<OPERATOR> GetOPERATORS()
        {
            string sql = string.Format("select * from OPERATORS ");
            return base.Database.SqlQuery<OPERATOR>(sql);
        }


        public IEnumerable<OPERATOR> GetOPERATORSByAgentNo(string AgentNo)
        {
            string where = string.Empty;
            if (AgentNo != "")
            {
                where = "where ISSUER_AGENT_NO = '" + AgentNo + "'";
            }
            string sql = string.Format("select * from OPERATORS {0}", where);
            return base.Database.SqlQuery<OPERATOR>(sql);
        }

        public string GetOBUNO(string obu_no)
        {
            string sql = string.Format("select OBU_NO from OBU_ISSUE where OBU_NO = '" + obu_no + "'");
            return base.Database.SqlQuery<string>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取操作员具有的权限
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Operator_FunctionInfo> GetOperatorFunctions(string operatorNo)
        {
            string sqlTemplate = @"SELECT 
                                DISTINCT OPERATOR_NO,C.MODULE_NAME ,C.PARENT_ID,C.MODULE_INDEX,C.MODULE_LEVEL, 
                                C.MODULE_RIGHT,C.MODULE_URI,C.URI_MESSAGE,C.ID  
                                FROM ISSUE_RPT.dbo.S_OPERATOR_ROLE AS A  
                                JOIN ISSUE_RPT.dbo.S_ROLE_MODULE AS B ON A.ROLE_ID = B.ROLE_ID  
                                JOIN ISSUE_RPT.dbo.S_MODULE_FUNCTION AS C ON B.MODULE_ID=C.ID  
                                WHERE  OPERATOR_NO='{0}' AND C.ID>12000 AND C.ID<13000
                                ORDER BY A.OPERATOR_NO,C.MODULE_INDEX";

            string sql = string.Format(sqlTemplate, operatorNo);
            return base.Database.SqlQuery<Operator_FunctionInfo>(sql);
        }

        public AgentInfo GetAgent(string clientIP)
        {
            string sqlTemplate = @"SELECT     p.ID, p.AGENT_NO as AgentNo, a.AGENT_NAME as AgentName, p.ADDRESS as ClientIP,a.AGENT_TYPE_NO as [Type]
                                    FROM         dbo.TABLE_POSINFO AS p LEFT OUTER JOIN dbo.ISSUER_AGENTS AS a ON a.AGENT_NO = p.AGENT_NO
                                    where  p.ADDRESS='{0}'";
            string sql = string.Format(sqlTemplate, clientIP);

            return base.Database.SqlQuery<AgentInfo>(sql).FirstOrDefault();
        }



        public IList<AgentInfo> GetAgents(string agentNO)
        {
            string strAgentNo = agentNO.IsNullOrEmpty() ? "" : " WHERE AGENT_NO='" + agentNO + "'";
            string sqlTemplate = @"select -1 as ID, a.AGENT_NO as AgentNo, a.AGENT_NAME as AgentName, null as ClientIP,Agent_Type_NO as Type from ISSUER_AGENTS a " + strAgentNo;
            string sql = sqlTemplate;

            return base.Database.SqlQuery<AgentInfo>(sql).ToList();
        }
        /// <summary>
        /// 根据IP获取营业厅 如果不为速通 则获取对应的营业厅类别
        /// </summary>
        /// <param name="strAgent"></param>
        /// <returns></returns>
        public IList<ValueText> GetAgentTypes(string strAgent)
        {
            string sqlTemplate = "";
            if (strAgent.Trim() == "")
                sqlTemplate = @"SELECT [AGENTTYPE_NO] as Value,[AGENTTYPE_NAME] as [Text] FROM [S_AGENT_TYPE]";
            else
                sqlTemplate = "SELECT [AGENTTYPE_NO] as Value,[AGENTTYPE_NAME] as [Text] FROM ISSUER_AGENTS AS A "
                    + " LEFT JOIN S_AGENT_TYPE AS B ON A.AGENT_TYPE_NO=B.AGENTTYPE_NO "
                    + " WHERE A.AGENT_NO='" + strAgent + "'";

            string sql = sqlTemplate;

            return base.Database.SqlQuery<ValueText>(sql).ToList();
        }

        public AgentInfo GetAgentByNo(string agentNo)
        {
            string sqlTemplate = @"SELECT     p.ID, p.AGENT_NO as AgentNo, a.AGENT_NAME as AgentName, p.ADDRESS as ClientIP
                                    FROM         dbo.TABLE_POSINFO AS p LEFT OUTER JOIN dbo.ISSUER_AGENTS AS a ON a.AGENT_NO = p.AGENT_NO
                                    where  p.AGENT_NO='{0}'";
            string sql = string.Format(sqlTemplate, agentNo);

            return base.Database.SqlQuery<AgentInfo>(sql).FirstOrDefault();
        }

        public AgentInfo GetAgentByPosID(long posid)
        {
            string sqlTemplate = @"SELECT     p.ID, p.AGENT_NO as AgentNo, a.AGENT_NAME as AgentName, p.ADDRESS as ClientIP
                                    FROM         dbo.TABLE_POSINFO AS p LEFT OUTER JOIN dbo.ISSUER_AGENTS AS a ON a.AGENT_NO = p.AGENT_NO
                                    where  p.ID='{0}'";
            string sql = string.Format(sqlTemplate, posid);

            return base.Database.SqlQuery<AgentInfo>(sql).FirstOrDefault();
        }
        public AgentInfo GetCenterAgentNo()
        {
            string sqlTemplate = " SELECT 1 ID,ISSUER_NO AS AgentNo,ISSUER_NAME AS AgentName,ISSUER_NO AS ClientIP,'' AS Type FROM dbo.ISSUERS ";

            string sql = string.Format(sqlTemplate);

            return base.Database.SqlQuery<AgentInfo>(sql).FirstOrDefault();
        }

        public IEnumerable<SPECS> GetSpecs(int supplierID)
        {
            try
            {
                string sql = "SELECT A.ID,A.SUPPLIER_ID,DEVICE_NAME,SPECS_IDENT,"
                + " A.SPEC_NAME+'  '+B.SOFT_VERSION+'  '+B.HARD_VERSION+'  '+B.KEY_VERSION SPEC_NAME ,A.REMARK,VERSION_ID "
                + " FROM OBU_CARE.DBO.SPECS AS A INNER JOIN OBU_CARE.DBO.DEVICE_VERSION AS B ON A.VERSION_ID=B.ID WHERE A.SUPPLIER_ID='" + supplierID + "'";
                return base.Database.SqlQuery<SPECS>(sql).ToList();
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        /// <summary>
        /// 根据标签号和客户编号获取更换的标签及首次发行时间
        /// </summary>
        /// <param name="lastNo"></param>
        /// <param name="userNo"></param>
        /// <returns></returns>
        public View_OBU_Info getRegisteBy(string lastNo, string userNo)
        {
            //只需获取关键的更换的标签和客编及发行时间、厂商和型号直接设置为空
            string sql = "SELECT '' SPEC_NAME,'' SUPPLIER,VEHICLE_PLATE_NO,OBU_NO,LAST_OBU_NO,"
                        + " USER_NO,VEHICLE_CLASS,'' VEHICLE_CLASS_NAME,'' USER_NAME,''SUPPLIER_ShortName,0 SUPPLIERS_ID,0 SPEC_ID,REGISTE_DATE  "
                      + " FROM OBU_ISSUE WHERE OBU_NO='" + lastNo + "' AND USER_NO='" + userNo + "'";
            return base.Database.SqlQuery<View_OBU_Info>(sql).FirstOrDefault();
        }

        public DateTime? GetRegisteDateByObuNo(string obuNo)
        {
            string sql = "SELECT RECORD_TIME FROM OPERATION_LOGS WHERE (CARD_ID=@OBU AND OPERATION_KIND=@TRADEIN_ISSUE) OR (REMARK =@OBU AND (OPERATION_KIND=@TRADEIN OR OPERATION_KIND=@TRADEIN_BAD))";
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@OBU",SqlDbType.VarChar,200),
                new SqlParameter("@TRADEIN_ISSUE",SqlDbType.Int),
                new SqlParameter("@TRADEIN",SqlDbType.Int),
                new SqlParameter("@TRADEIN_BAD",SqlDbType.Int)
            };
            parameters[0].Value = obuNo;
            parameters[1].Value = (int)OpKindObuTradein.标签以旧换新发行;
            parameters[2].Value = (int)OpKindObuTradein.标签以旧换新_标签损坏;
            parameters[3].Value = (int)OpKindObuTradein.标签以旧换新_标签正常;
            return base.Database.SqlQuery<DateTime?>(sql,parameters).FirstOrDefault();
        }

        /// <summary>
        /// 根据标签号查询以旧换翻新的日期
        /// </summary>
        /// <param name="obuNo"></param>
        /// <returns></returns>
        public DateTime? GetDateForOldOfOld(string obuNo)
        {
            string sql = "SELECT RECORD_TIME FROM OPERATION_LOGS WHERE (CARD_ID=@OBU AND (OPERATION_KIND=@TRADEIN_ISSUE OR OPERATION_KIND=@NEW_VERSION)) OR (REMARK =@OBU AND (OPERATION_KIND=@TRADEIN OR OPERATION_KIND=@TRADEIN_BAD))";
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@OBU",SqlDbType.VarChar,200),
                new SqlParameter("@TRADEIN_ISSUE",SqlDbType.Int),
                new SqlParameter("@TRADEIN",SqlDbType.Int),
                new SqlParameter("@TRADEIN_BAD",SqlDbType.Int),
                new SqlParameter("@NEW_VERSION",SqlDbType.Int)
            };
            parameters[0].Value = obuNo;
            parameters[1].Value = (int)OpKindObuTradein.标签以旧换翻新发行;
            parameters[2].Value = (int)OpKindObuTradein.标签以旧换翻新更换;
            parameters[3].Value = (int)OpKindObuTradein.标签以旧换翻新更换_标签损坏;
            parameters[4].Value = (int)OpKindObuTradein.标签密钥升级;
            return base.Database.SqlQuery<DateTime?>(sql, parameters).FirstOrDefault();
        }
    }
}
